package com.kakarote.hrm.controller.reportForms;


import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.kakarote.core.common.Result;
import com.kakarote.hrm.entity.PO.HrmDept;
import com.kakarote.hrm.entity.VO.reportForms.EmployeeNumVO;
import com.kakarote.hrm.service.reportForms.impl.HrmSalaryReportService;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Comparator;
import java.util.Date;
import java.util.List;
import java.util.stream.Collectors;

@RestController
@RequestMapping("/hrmSalaryReport")
@Slf4j
@Api(tags = "统计报表-导出")
public class HrmSalaryReportController {

    @Autowired
    private HrmSalaryReportService hrmSalaryReportService;

    @PostMapping("/manpowerNum")
    @ApiOperation("人力资源效益指标")
    public Result<List<JSONObject>> manpowerNum() {
        List<JSONObject> res = hrmSalaryReportService.queryManPowerNum();
        return Result.ok(res);
    }

    @PostMapping("/queryEmployeeNum")
    @ApiOperation("在岗职工人数、劳务派遣人数、劳务外包和其他从业人员人数")
    public Result<List<EmployeeNumVO>> queryqueryEmployeeNum() {
        List<EmployeeNumVO> result = hrmSalaryReportService.queryEmployeeNum();
        return Result.ok(result);
    }


    @PostMapping("/queryPerIncome")
    @ApiOperation("人均工资")
    public Result<List<JSONObject>> queryPerIncome() {
        HrmDept dept= new HrmDept();
        dept.setLevel(2);
        List<HrmDept> result = hrmSalaryReportService.selectDeptList(dept);
        List<JSONObject> deptList=new ArrayList<>();
        for(HrmDept de:result){
            JSONObject js=new JSONObject();
            js.put("deptId",de.getDeptId());
            js.put("deptName",de.getName());
            js.put("totalIncome","--");
            js.put("highIncome","--");
            js.put("middleIncome","--");
            js.put("normalIncome","--");
            js.put("totalNum","--");
            js.put("highNum","--");
            js.put("middleNum","--");
            js.put("normalNum","--");
            js.put("employeePerIncome","--");
            js.put("middlePerIncome","--");
            js.put("normalPerIncome","--");
            js.put("remark","--");
            deptList.add(js);
        }
        return Result.ok(deptList);
    }

    @PostMapping("/querySalary")
    @ApiOperation("薪酬")
    public Result<List<JSONObject>> querySalary() {
        HrmDept dept= new HrmDept();
        dept.setLevel(2);
        List<HrmDept> result = hrmSalaryReportService.selectDeptList(dept);
        List<JSONObject> deptList=new ArrayList<>();
        for(HrmDept de:result){
            JSONObject js=new JSONObject();
            js.put("deptId",de.getDeptId());
            js.put("deptName",de.getName());

            js.put("subTotalPart1","--");
            js.put("employeeNum","--");
            js.put("dispatchNum","--");
            js.put("otherNum","--");

            js.put("subTotalPart2","--");
            js.put("basicSalary","--");
            js.put("performanceSalary","--");
            js.put("otherSalary","--");
            js.put("otherBonus","--");

            js.put("welfare","--");

            js.put("subTotalPart3","--");
            js.put("fiveInsurances","--");
            js.put("accumulationFund","--");
            js.put("enterpriseAnnuity","--");

            js.put("laborCost","--");
            js.put("dispatch","--");
            js.put("other","--");
            js.put("employTotalSalary","--");
            js.put("employPerSalary","--");
            js.put("perCost","--");
            js.put("normalPerCost","--");
            deptList.add(js);
        }
        return Result.ok(deptList);
    }

    @PostMapping("/queryNoWorkSalary")
    @ApiOperation("内退、不在岗员工薪酬统计")
    public Result<List<JSONObject>> queryNoWorkSalary() {
        HrmDept dept= new HrmDept();
        dept.setLevel(2);
        List<HrmDept> result = hrmSalaryReportService.selectDeptList(dept);
        List<JSONObject> deptList=new ArrayList<>();
        for(HrmDept de:result){
            JSONObject js=new JSONObject();
            js.put("deptId",de.getDeptId());
            js.put("deptName",de.getName());
            js.put("retireNum","--");
            js.put("noWorkNum","--");
            js.put("subTotalNum","--");
            js.put("salary","--");
            js.put("welfare","--");
            js.put("fiveInsurances","--");
            js.put("accumulationFund","--");
            js.put("enterpriseAnnuity","--");
            js.put("subTotal","--");
            js.put("total","--");
            js.put("perCost","--");
            deptList.add(js);
        }
        return Result.ok(deptList);
    }

    @PostMapping("/queryRetireSalary")
    @ApiOperation("离、退休员工薪酬统计")
    public Result<List<JSONObject>> queryRetireSalary() {
        HrmDept dept= new HrmDept();
        dept.setLevel(2);
        List<HrmDept> result = hrmSalaryReportService.selectDeptList(dept);
        List<JSONObject> deptList=new ArrayList<>();
        for(HrmDept de:result){
            JSONObject js=new JSONObject();
            js.put("deptId",de.getDeptId());
            js.put("deptName",de.getName());
            js.put("retireNum","--");
            js.put("subsidy","--");
            js.put("welfare","--");
            js.put("total","--");
            js.put("perCost","--");
            deptList.add(js);
        }
        return Result.ok(deptList);
    }

    @PostMapping("/excelSalaryExport")
    @ApiOperation("从业人员薪酬统计导出")
    public void excelExport( HttpServletResponse response,String deptId) {
        //EmployeeNumVO result = hrmSalaryReportService.queryEmployeeNum(deptId);
        try {
            HrmDept dept= new HrmDept();
            dept.setLevel(2);
            List<HrmDept> result = hrmSalaryReportService.selectDeptList(dept);
            ExcelWriter writer = ExcelUtil.getWriter();
            for(int i=0;i<22;i++){writer.setColumnWidth(i,30);}
            Sheet sheet = writer.getSheet();
            CellRangeAddress cellAddresses=new CellRangeAddress(0,0,0,22);
            sheet.addMergedRegion(cellAddresses);

            /*标题以及内容样式 begin*/
            CellStyle headStyle = writer.createCellStyle();
            Font font =writer.createFont();
            font.setBold(true);
            font.setFontHeightInPoints((short) 16);
            headStyle.setFont(font);
            headStyle.setAlignment(HorizontalAlignment.CENTER);
            headStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
            headStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            headStyle.setBorderTop(BorderStyle.THIN);
            headStyle.setBorderLeft(BorderStyle.THIN);
            headStyle.setBorderRight(BorderStyle.THIN);
            headStyle.setBorderBottom(BorderStyle.THIN);

            CellStyle dataStyle = writer.createCellStyle();
            dataStyle.setBorderTop(BorderStyle.THIN);
            dataStyle.setBorderLeft(BorderStyle.THIN);
            dataStyle.setBorderRight(BorderStyle.THIN);
            dataStyle.setBorderBottom(BorderStyle.THIN);
            dataStyle.setAlignment(HorizontalAlignment.CENTER);
            dataStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            dataStyle.setWrapText(true);
            Font dataFont =writer.createFont();
            dataFont.setFontHeightInPoints((short) 14);
            dataStyle.setFont(dataFont);
            /*标题以及内容样式 end*/

            /*在岗职工人数、劳务派遣人数、劳务外包和其他从业人员人数 表格begin*/
            Row row0=sheet.createRow(0);
            row0.setHeightInPoints(30);
            Cell cell0=row0.createCell(0);
            cell0.setCellValue("xxxx年市政公用集团从业人员薪酬统计表-汇总");
            cell0.setCellStyle(headStyle);

            String titleArray1[]=new String[]{"序号","单位名称","从业人员平均人数","在岗职工工资总额","福利费","五险二金（单位部分）","···","劳务派遣员工工资总额","劳务外包和其他从业人员工资总额","从业人员人工成本合计","在岗职工人均工资","人均成本（含外包和派遣）","人均成本（不含外包和派遣）"};
            Row row1=sheet.createRow(1);
            row1.setHeightInPoints(25);


            Cell cell=row1.createCell(0);
            cell.setCellValue("序号");
            cell.setCellStyle(dataStyle);
            sheet.addMergedRegion(new CellRangeAddress(1,2,0,0));

            Cell cell1=row1.createCell(1);
            cell1.setCellValue("单位名称");
            cell1.setCellStyle(dataStyle);
            sheet.addMergedRegion(new CellRangeAddress(1,2,1,1));

            Cell cell2=row1.createCell(2);
            cell2.setCellValue("从业人员平均人数");
            cell2.setCellStyle(dataStyle);
            sheet.addMergedRegion(new CellRangeAddress(1,1,2,5));

            Cell cell3=row1.createCell(3);
            cell3.setCellValue("");
            cell3.setCellStyle(dataStyle);

            Cell cell4=row1.createCell(4);
            cell4.setCellValue("");
            cell4.setCellStyle(dataStyle);

            Cell cell5=row1.createCell(5);
            cell5.setCellValue("");
            cell5.setCellStyle(dataStyle);

            Cell cell6=row1.createCell(6);
            cell6.setCellValue("在岗职工工资总额");
            cell6.setCellStyle(dataStyle);
            sheet.addMergedRegion(new CellRangeAddress(1,1,6,10));

            Cell cell7=row1.createCell(7);
            cell7.setCellValue("");
            cell7.setCellStyle(dataStyle);

            Cell cell8=row1.createCell(8);
            cell8.setCellValue("");
            cell8.setCellStyle(dataStyle);

            Cell cell9=row1.createCell(9);
            cell9.setCellValue("");
            cell9.setCellStyle(dataStyle);

            Cell cell10=row1.createCell(10);
            cell10.setCellValue("");
            cell10.setCellStyle(dataStyle);

            Cell cell11=row1.createCell(11);
            cell11.setCellValue("福利费");
            cell11.setCellStyle(dataStyle);
            sheet.addMergedRegion(new CellRangeAddress(1,2,11,11));

            Cell cell12=row1.createCell(12);
            cell12.setCellValue("五险二金（单位部分）");
            cell12.setCellStyle(dataStyle);
            sheet.addMergedRegion(new CellRangeAddress(1,1,12,15));

            Cell cell16=row1.createCell(16);
            cell16.setCellValue("···");
            cell16.setCellStyle(dataStyle);
            sheet.addMergedRegion(new CellRangeAddress(1,2,16,16));

            Cell cell17=row1.createCell(17);
            cell17.setCellValue("劳务派遣员工工资总额");
            cell17.setCellStyle(dataStyle);
            sheet.addMergedRegion(new CellRangeAddress(1,2,17,17));

            Cell cell18=row1.createCell(18);
            cell18.setCellValue("劳务外包和其他从业人员工资总额");
            cell18.setCellStyle(dataStyle);
            sheet.addMergedRegion(new CellRangeAddress(1,2,18,18));

            Cell cell19=row1.createCell(19);
            cell19.setCellValue("从业人员人工成本合计");
            cell19.setCellStyle(dataStyle);
            sheet.addMergedRegion(new CellRangeAddress(1,2,19,19));

            Cell cell20=row1.createCell(20);
            cell20.setCellValue("在岗职工人均工资");
            cell20.setCellStyle(dataStyle);
            sheet.addMergedRegion(new CellRangeAddress(1,2,20,20));

            Cell cell21=row1.createCell(21);
            cell21.setCellValue("人均成本（含外包和派遣）");
            cell21.setCellStyle(dataStyle);
            sheet.addMergedRegion(new CellRangeAddress(1,2,21,21));

            Cell cell22=row1.createCell(22);
            cell22.setCellValue("人均成本（不含外包和派遣）");
            cell22.setCellStyle(dataStyle);
            sheet.addMergedRegion(new CellRangeAddress(1,2,22,22));


            Row row2=sheet.createRow(2);
            row2.setHeightInPoints(25);

            Cell cel=row2.createCell(0);
            cel.setCellValue("");
            cel.setCellStyle(dataStyle);

            Cell cel0=row2.createCell(1);
            cel0.setCellValue("");
            cel0.setCellStyle(dataStyle);


            Cell cel1=row2.createCell(2);
            cel1.setCellValue("小计");
            cel1.setCellStyle(dataStyle);

            Cell cel2=row2.createCell(3);
            cel2.setCellValue("在岗职工人数");
            cel2.setCellStyle(dataStyle);

            Cell cel3=row2.createCell(4);
            cel3.setCellValue("劳务派遣人数");
            cel3.setCellStyle(dataStyle);

            Cell cel5=row2.createCell(5);
            cel5.setCellValue("劳务外包和其他从业人员人数");
            cel5.setCellStyle(dataStyle);

            Cell cel6=row2.createCell(6);
            cel6.setCellValue("小计");
            cel6.setCellStyle(dataStyle);

            Cell cel7=row2.createCell(7);
            cel7.setCellValue("基本工资");
            cel7.setCellStyle(dataStyle);

            Cell cel8=row2.createCell(8);
            cel8.setCellValue("绩效工资");
            cel8.setCellStyle(dataStyle);

            Cell cel9=row2.createCell(9);
            cel9.setCellValue("其他工资（补贴类）");
            cel9.setCellStyle(dataStyle);

            Cell cel10=row2.createCell(10);
            cel10.setCellValue("其他类奖金");
            cel10.setCellStyle(dataStyle);

            Cell cel11=row2.createCell(11);
            cel11.setCellValue("");
            cel11.setCellStyle(dataStyle);

            Cell cel12=row2.createCell(12);
            cel12.setCellValue("小计");
            cel12.setCellStyle(dataStyle);

            Cell cel13=row2.createCell(13);
            cel13.setCellValue("五险");
            cel13.setCellStyle(dataStyle);

            Cell cel14=row2.createCell(14);
            cel14.setCellValue("公积金");
            cel14.setCellStyle(dataStyle);

            Cell cel15=row2.createCell(15);
            cel15.setCellValue("企业年金");
            cel15.setCellStyle(dataStyle);

            Cell cel16=row2.createCell(16);
            cel16.setCellValue("");
            cel16.setCellStyle(dataStyle);

            Cell cel17=row2.createCell(17);
            cel17.setCellValue("");
            cel17.setCellStyle(dataStyle);

            Cell cel18=row2.createCell(18);
            cel18.setCellValue("");
            cel18.setCellStyle(dataStyle);

            Cell cel19=row2.createCell(19);
            cel19.setCellValue("");
            cel19.setCellStyle(dataStyle);

            Cell cel20=row2.createCell(20);
            cel20.setCellValue("");
            cel20.setCellStyle(dataStyle);

            Cell cel21=row2.createCell(21);
            cel21.setCellValue("");
            cel21.setCellStyle(dataStyle);

            Cell cel22=row2.createCell(22);
            cel22.setCellValue("");
            cel22.setCellStyle(dataStyle);

            int x=2;
            for(int i=0;i<result.size();i++){
                x++;
                HrmDept dt=result.get(i);
                Row row3=sheet.createRow(x);
                row3.setHeightInPoints(25);
                Cell celll=row3.createCell(0);
                celll.setCellValue(i+1);
                celll.setCellStyle(dataStyle);

                Cell celll1=row3.createCell(1);
                celll1.setCellValue(dt.getName());
                celll1.setCellStyle(dataStyle);

                Cell celll2=row3.createCell(2);
                celll2.setCellValue("");
                celll2.setCellStyle(dataStyle);

                Cell celll3=row3.createCell(3);
                celll3.setCellValue("");
                celll3.setCellStyle(dataStyle);

                Cell celll4=row3.createCell(4);
                celll4.setCellValue("");
                celll4.setCellStyle(dataStyle);

                Cell celll5=row3.createCell(5);
                celll5.setCellValue("");
                celll5.setCellStyle(dataStyle);

                Cell celll6=row3.createCell(6);
                celll6.setCellValue("");
                celll6.setCellStyle(dataStyle);

                Cell celll7=row3.createCell(7);
                celll7.setCellValue("");
                celll7.setCellStyle(dataStyle);

                Cell celll8=row3.createCell(8);
                celll8.setCellValue("");
                celll8.setCellStyle(dataStyle);

                Cell celll9=row3.createCell(9);
                celll9.setCellValue("");
                celll9.setCellStyle(dataStyle);

                Cell celll10=row3.createCell(10);
                celll10.setCellValue("");
                celll10.setCellStyle(dataStyle);

                Cell celll11=row3.createCell(11);
                celll11.setCellValue("");
                celll11.setCellStyle(dataStyle);

                Cell celll12=row3.createCell(12);
                celll12.setCellValue("");
                celll12.setCellStyle(dataStyle);

                Cell celll13=row3.createCell(13);
                celll13.setCellValue("");
                celll13.setCellStyle(dataStyle);

                Cell celll14=row3.createCell(14);
                celll14.setCellValue("");
                celll14.setCellStyle(dataStyle);

                Cell celll15=row3.createCell(15);
                celll15.setCellValue("");
                celll15.setCellStyle(dataStyle);

                Cell celll16=row3.createCell(16);
                celll16.setCellValue("");
                celll16.setCellStyle(dataStyle);

                Cell celll17=row3.createCell(17);
                celll17.setCellValue("");
                celll17.setCellStyle(dataStyle);

                Cell celll18=row3.createCell(18);
                celll18.setCellValue("");
                celll18.setCellStyle(dataStyle);

                Cell celll19=row3.createCell(19);
                celll19.setCellValue("");
                celll19.setCellStyle(dataStyle);

                Cell celll20=row3.createCell(20);
                celll20.setCellValue("");
                celll20.setCellStyle(dataStyle);

                Cell celll21=row3.createCell(21);
                celll21.setCellValue("");
                celll21.setCellStyle(dataStyle);

                Cell celll22=row3.createCell(22);
                celll22.setCellValue("");
                celll22.setCellStyle(dataStyle);

            }

            long timestamp = new Date().getTime();
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setCharacterEncoding("UTF-8");
            //test.xls是弹出下载对话框的文件名，不能为中文，中文请自行编码
            response.setHeader("Content-Disposition", "attachment;filename="+timestamp+"salary_export.xls");
            ServletOutputStream out = response.getOutputStream();
            writer.flush(out);
        }catch (Exception e){
            log.error("导出统计报表-薪酬记录错误：", e);
        }
    }


    @PostMapping("/notOnDutyExport")
    @ApiOperation("内退、不在岗员工薪酬统计报表导出")
    public void notOnDutyExport( HttpServletResponse response,String deptId) {
        //EmployeeNumVO result = hrmSalaryReportService.queryEmployeeNum(deptId);
        try {
            HrmDept dept= new HrmDept();
            dept.setLevel(2);
            List<HrmDept> result = hrmSalaryReportService.selectDeptList(dept);
            ExcelWriter writer = ExcelUtil.getWriter();
            for(int i=0;i<12;i++){writer.setColumnWidth(i,30);}
            Sheet sheet = writer.getSheet();
            CellRangeAddress cellAddresses=new CellRangeAddress(0,0,0,12);
            sheet.addMergedRegion(cellAddresses);

            /*标题以及内容样式 begin*/
            CellStyle headStyle = writer.createCellStyle();
            Font font =writer.createFont();
            font.setBold(true);
            font.setFontHeightInPoints((short) 16);
            headStyle.setFont(font);
            headStyle.setAlignment(HorizontalAlignment.CENTER);
            headStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
            headStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            headStyle.setBorderTop(BorderStyle.THIN);
            headStyle.setBorderLeft(BorderStyle.THIN);
            headStyle.setBorderRight(BorderStyle.THIN);
            headStyle.setBorderBottom(BorderStyle.THIN);

            CellStyle dataStyle = writer.createCellStyle();
            dataStyle.setBorderTop(BorderStyle.THIN);
            dataStyle.setBorderLeft(BorderStyle.THIN);
            dataStyle.setBorderRight(BorderStyle.THIN);
            dataStyle.setBorderBottom(BorderStyle.THIN);
            dataStyle.setAlignment(HorizontalAlignment.CENTER);
            dataStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            dataStyle.setWrapText(true);
            Font dataFont =writer.createFont();
            dataFont.setFontHeightInPoints((short) 14);
            dataStyle.setFont(dataFont);
            /*标题以及内容样式 end*/

            /*在岗职工人数、劳务派遣人数、劳务外包和其他从业人员人数 表格begin*/
            Row row0=sheet.createRow(0);
            row0.setHeightInPoints(30);
            Cell cell0=row0.createCell(0);
            cell0.setCellValue("xxxx年市政公用集团内退、不在岗员工薪酬统计表-汇总");
            cell0.setCellStyle(headStyle);

            String titleArray1[]=new String[]{"序号","单位名称","从业人员平均人数","在岗职工工资总额","福利费","五险二金（单位部分）","···","劳务派遣员工工资总额","劳务外包和其他从业人员工资总额","从业人员人工成本合计","在岗职工人均工资","人均成本（含外包和派遣）","人均成本（不含外包和派遣）"};
            Row row1=sheet.createRow(1);
            row1.setHeightInPoints(25);


            Cell cell=row1.createCell(0);
            cell.setCellValue("序号");
            cell.setCellStyle(dataStyle);
            sheet.addMergedRegion(new CellRangeAddress(1,2,0,0));

            Cell cell1=row1.createCell(1);
            cell1.setCellValue("单位名称");
            cell1.setCellStyle(dataStyle);
            sheet.addMergedRegion(new CellRangeAddress(1,2,1,1));

            Cell cell2=row1.createCell(2);
            cell2.setCellValue("人数");
            cell2.setCellStyle(dataStyle);
            sheet.addMergedRegion(new CellRangeAddress(1,1,2,4));

            Cell cell3=row1.createCell(3);
            cell3.setCellValue("");
            cell3.setCellStyle(dataStyle);

            Cell cell4=row1.createCell(4);
            cell4.setCellValue("");
            cell4.setCellStyle(dataStyle);

            Cell cell5=row1.createCell(5);
            cell5.setCellValue("工资及奖金");
            cell5.setCellStyle(dataStyle);
            sheet.addMergedRegion(new CellRangeAddress(1,2,5,5));

            Cell cell6=row1.createCell(6);
            cell6.setCellValue("福利费");
            cell6.setCellStyle(dataStyle);
            sheet.addMergedRegion(new CellRangeAddress(1,2,6,6));

            Cell cell7=row1.createCell(7);
            cell7.setCellValue("五险二金（单位部分）");
            cell7.setCellStyle(dataStyle);
            sheet.addMergedRegion(new CellRangeAddress(1,1,7,10));

            Cell cell8=row1.createCell(8);
            cell8.setCellValue("");
            cell8.setCellStyle(dataStyle);

            Cell cell9=row1.createCell(9);
            cell9.setCellValue("");
            cell9.setCellStyle(dataStyle);

            Cell cell10=row1.createCell(10);
            cell10.setCellValue("");
            cell10.setCellStyle(dataStyle);

            Cell cell11=row1.createCell(11);
            cell11.setCellValue("合计");
            cell11.setCellStyle(dataStyle);
            sheet.addMergedRegion(new CellRangeAddress(1,2,11,11));

            Cell cell12=row1.createCell(12);
            cell12.setCellValue("人均成本");
            cell12.setCellStyle(dataStyle);
            sheet.addMergedRegion(new CellRangeAddress(1,2,12,12));

            Row row2=sheet.createRow(2);
            row2.setHeightInPoints(25);

            Cell cel=row2.createCell(0);
            cel.setCellValue("");
            cel.setCellStyle(dataStyle);

            Cell cel0=row2.createCell(1);
            cel0.setCellValue("");
            cel0.setCellStyle(dataStyle);


            Cell cel1=row2.createCell(2);
            cel1.setCellValue("内退");
            cel1.setCellStyle(dataStyle);

            Cell cel2=row2.createCell(3);
            cel2.setCellValue("不在岗");
            cel2.setCellStyle(dataStyle);

            Cell cel3=row2.createCell(4);
            cel3.setCellValue("小计");
            cel3.setCellStyle(dataStyle);

            Cell cel5=row2.createCell(5);
            cel5.setCellValue("");
            cel5.setCellStyle(dataStyle);

            Cell cel6=row2.createCell(6);
            cel6.setCellValue("");
            cel6.setCellStyle(dataStyle);

            Cell cel7=row2.createCell(7);
            cel7.setCellValue("五险");
            cel7.setCellStyle(dataStyle);

            Cell cel8=row2.createCell(8);
            cel8.setCellValue("公积金");
            cel8.setCellStyle(dataStyle);

            Cell cel9=row2.createCell(9);
            cel9.setCellValue("企业年金");
            cel9.setCellStyle(dataStyle);

            Cell cel10=row2.createCell(10);
            cel10.setCellValue("小计");
            cel10.setCellStyle(dataStyle);

            Cell cel11=row2.createCell(11);
            cel11.setCellValue("");
            cel11.setCellStyle(dataStyle);

            Cell cel12=row2.createCell(12);
            cel12.setCellValue("");
            cel12.setCellStyle(dataStyle);

            int x=2;
            for(int i=0;i<result.size();i++){
                x++;
                HrmDept dt=result.get(i);
                Row row3=sheet.createRow(x);
                row3.setHeightInPoints(25);
                Cell celll=row3.createCell(0);
                celll.setCellValue(i+1);
                celll.setCellStyle(dataStyle);

                Cell celll1=row3.createCell(1);
                celll1.setCellValue(dt.getName());
                celll1.setCellStyle(dataStyle);

                Cell celll2=row3.createCell(2);
                celll2.setCellValue("");
                celll2.setCellStyle(dataStyle);

                Cell celll3=row3.createCell(3);
                celll3.setCellValue("");
                celll3.setCellStyle(dataStyle);

                Cell celll4=row3.createCell(4);
                celll4.setCellValue("");
                celll4.setCellStyle(dataStyle);

                Cell celll5=row3.createCell(5);
                celll5.setCellValue("");
                celll5.setCellStyle(dataStyle);

                Cell celll6=row3.createCell(6);
                celll6.setCellValue("");
                celll6.setCellStyle(dataStyle);

                Cell celll7=row3.createCell(7);
                celll7.setCellValue("");
                celll7.setCellStyle(dataStyle);

                Cell celll8=row3.createCell(8);
                celll8.setCellValue("");
                celll8.setCellStyle(dataStyle);

                Cell celll9=row3.createCell(9);
                celll9.setCellValue("");
                celll9.setCellStyle(dataStyle);

                Cell celll10=row3.createCell(10);
                celll10.setCellValue("");
                celll10.setCellStyle(dataStyle);

                Cell celll11=row3.createCell(11);
                celll11.setCellValue("");
                celll11.setCellStyle(dataStyle);

                Cell celll12=row3.createCell(12);
                celll12.setCellValue("");
                celll12.setCellStyle(dataStyle);
            }

            long timestamp = new Date().getTime();
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setCharacterEncoding("UTF-8");
            //test.xls是弹出下载对话框的文件名，不能为中文，中文请自行编码
            response.setHeader("Content-Disposition", "attachment;filename="+timestamp+"notOnDutyExport.xls");
            ServletOutputStream out = response.getOutputStream();
            writer.flush(out);
        }catch (Exception e){
            log.error("导出统计报表-薪酬记录错误：", e);
        }
    }


    @PostMapping("/retirementExport")
    @ApiOperation("离、退休员工薪酬统计报表导出")
    public void retirementExport( HttpServletResponse response,String deptId) {
        //EmployeeNumVO result = hrmSalaryReportService.queryEmployeeNum(deptId);
        try {
            HrmDept dept= new HrmDept();
            dept.setLevel(2);
            List<HrmDept> result = hrmSalaryReportService.selectDeptList(dept);
            ExcelWriter writer = ExcelUtil.getWriter();
            for(int i=0;i<6;i++){writer.setColumnWidth(i,30);}
            Sheet sheet = writer.getSheet();
            CellRangeAddress cellAddresses=new CellRangeAddress(0,0,0,6);
            sheet.addMergedRegion(cellAddresses);

            /*标题以及内容样式 begin*/
            CellStyle headStyle = writer.createCellStyle();
            Font font =writer.createFont();
            font.setBold(true);
            font.setFontHeightInPoints((short) 16);
            headStyle.setFont(font);
            headStyle.setAlignment(HorizontalAlignment.CENTER);
            headStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
            headStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            headStyle.setBorderTop(BorderStyle.THIN);
            headStyle.setBorderLeft(BorderStyle.THIN);
            headStyle.setBorderRight(BorderStyle.THIN);
            headStyle.setBorderBottom(BorderStyle.THIN);

            CellStyle dataStyle = writer.createCellStyle();
            dataStyle.setBorderTop(BorderStyle.THIN);
            dataStyle.setBorderLeft(BorderStyle.THIN);
            dataStyle.setBorderRight(BorderStyle.THIN);
            dataStyle.setBorderBottom(BorderStyle.THIN);
            dataStyle.setAlignment(HorizontalAlignment.CENTER);
            dataStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            dataStyle.setWrapText(true);
            Font dataFont =writer.createFont();
            dataFont.setFontHeightInPoints((short) 14);
            dataStyle.setFont(dataFont);
            /*标题以及内容样式 end*/

            /*在岗职工人数、劳务派遣人数、劳务外包和其他从业人员人数 表格begin*/
            Row row0=sheet.createRow(0);
            row0.setHeightInPoints(30);
            Cell cell0=row0.createCell(0);
            cell0.setCellValue("xxxx年市政公用集团离、退休员工薪酬统计表-汇总");
            cell0.setCellStyle(headStyle);

            Row row1=sheet.createRow(1);
            row1.setHeightInPoints(25);

            Cell cell=row1.createCell(0);
            cell.setCellValue("序号");
            cell.setCellStyle(dataStyle);

            Cell cell1=row1.createCell(1);
            cell1.setCellValue("单位名称");
            cell1.setCellStyle(dataStyle);

            Cell cell2=row1.createCell(2);
            cell2.setCellValue("人数");
            cell2.setCellStyle(dataStyle);

            Cell cell3=row1.createCell(3);
            cell3.setCellValue("补贴");
            cell3.setCellStyle(dataStyle);

            Cell cell4=row1.createCell(4);
            cell4.setCellValue("福利费");
            cell4.setCellStyle(dataStyle);

            Cell cell5=row1.createCell(5);
            cell5.setCellValue("合计");
            cell5.setCellStyle(dataStyle);

            Cell cell6=row1.createCell(6);
            cell6.setCellValue("人均成本");
            cell6.setCellStyle(dataStyle);

            int x=1;
            for(int i=0;i<result.size();i++){
                x++;
                HrmDept dt=result.get(i);
                Row row3=sheet.createRow(x);
                row3.setHeightInPoints(25);
                Cell celll=row3.createCell(0);
                celll.setCellValue(i+1);
                celll.setCellStyle(dataStyle);

                Cell celll1=row3.createCell(1);
                celll1.setCellValue(dt.getName());
                celll1.setCellStyle(dataStyle);

                Cell celll2=row3.createCell(2);
                celll2.setCellValue("");
                celll2.setCellStyle(dataStyle);

                Cell celll3=row3.createCell(3);
                celll3.setCellValue("");
                celll3.setCellStyle(dataStyle);

                Cell celll4=row3.createCell(4);
                celll4.setCellValue("");
                celll4.setCellStyle(dataStyle);

                Cell celll5=row3.createCell(5);
                celll5.setCellValue("");
                celll5.setCellStyle(dataStyle);

                Cell celll6=row3.createCell(6);
                celll6.setCellValue("");
                celll6.setCellStyle(dataStyle);

            }



            long timestamp = new Date().getTime();
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setCharacterEncoding("UTF-8");
            //test.xls是弹出下载对话框的文件名，不能为中文，中文请自行编码
            response.setHeader("Content-Disposition", "attachment;filename="+timestamp+"retirementExport.xls");
            ServletOutputStream out = response.getOutputStream();
            writer.flush(out);
        }catch (Exception e){
            log.error("导出统计报表-薪酬记录错误：", e);
        }
    }



    @PostMapping("/perCapitaSalaryExport")
    @ApiOperation("在岗职工人均工资统计报表导出")
    public void perCapitaSalaryExport( HttpServletResponse response,String deptId) {
        //EmployeeNumVO result = hrmSalaryReportService.queryEmployeeNum(deptId);
        try {
            HrmDept dept= new HrmDept();
            dept.setLevel(2);
            List<HrmDept> result = hrmSalaryReportService.selectDeptList(dept);
            ExcelWriter writer = ExcelUtil.getWriter();
            for(int i=0;i<13;i++){writer.setColumnWidth(i,30);}
            Sheet sheet = writer.getSheet();
            CellRangeAddress cellAddresses=new CellRangeAddress(0,0,0,13);
            sheet.addMergedRegion(cellAddresses);

            /*标题以及内容样式 begin*/
            CellStyle headStyle = writer.createCellStyle();
            Font font =writer.createFont();
            font.setBold(true);
            font.setFontHeightInPoints((short) 16);
            headStyle.setFont(font);
            headStyle.setAlignment(HorizontalAlignment.CENTER);
            headStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
            headStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            headStyle.setBorderTop(BorderStyle.THIN);
            headStyle.setBorderLeft(BorderStyle.THIN);
            headStyle.setBorderRight(BorderStyle.THIN);
            headStyle.setBorderBottom(BorderStyle.THIN);

            CellStyle dataStyle = writer.createCellStyle();
            dataStyle.setBorderTop(BorderStyle.THIN);
            dataStyle.setBorderLeft(BorderStyle.THIN);
            dataStyle.setBorderRight(BorderStyle.THIN);
            dataStyle.setBorderBottom(BorderStyle.THIN);
            dataStyle.setAlignment(HorizontalAlignment.CENTER);
            dataStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            dataStyle.setWrapText(true);
            Font dataFont =writer.createFont();
            dataFont.setFontHeightInPoints((short) 14);
            dataStyle.setFont(dataFont);
            /*标题以及内容样式 end*/

            /*在岗职工人数、劳务派遣人数、劳务外包和其他从业人员人数 表格begin*/
            Row row0=sheet.createRow(0);
            row0.setHeightInPoints(30);
            Cell cell0=row0.createCell(0);
            cell0.setCellValue("在岗职工人均工资统计表");
            cell0.setCellStyle(headStyle);

            Row row1=sheet.createRow(1);
            row1.setHeightInPoints(25);

            Cell cell=row1.createCell(0);
            cell.setCellValue("序号");
            cell.setCellStyle(dataStyle);
            sheet.addMergedRegion(new CellRangeAddress(1,2,0,0));

            Cell cell1=row1.createCell(1);
            cell1.setCellValue("单位名称");
            cell1.setCellStyle(dataStyle);
            sheet.addMergedRegion(new CellRangeAddress(1,2,1,1));

            Cell cell2=row1.createCell(2);
            cell2.setCellValue("工资总额");
            cell2.setCellStyle(dataStyle);
            sheet.addMergedRegion(new CellRangeAddress(1,1,2,5));

            Cell cell3=row1.createCell(3);
            cell3.setCellValue("");
            cell3.setCellStyle(dataStyle);

            Cell cell4=row1.createCell(4);
            cell4.setCellValue("");
            cell4.setCellStyle(dataStyle);

            Cell cell5=row1.createCell(5);
            cell5.setCellValue("");
            cell5.setCellStyle(dataStyle);

            Cell cell6=row1.createCell(6);
            cell6.setCellValue("在岗职工年平均人数");
            cell6.setCellStyle(dataStyle);
            sheet.addMergedRegion(new CellRangeAddress(1,1,6,9));

            Cell cell10=row1.createCell(10);
            cell10.setCellValue("在岗职工年平均工资");
            cell10.setCellStyle(dataStyle);
            sheet.addMergedRegion(new CellRangeAddress(1,2,10,10));

            Cell cell11=row1.createCell(11);
            cell11.setCellValue("中层管理人员年平均工资");
            cell11.setCellStyle(dataStyle);
            sheet.addMergedRegion(new CellRangeAddress(1,2,11,11));

            Cell cell12=row1.createCell(12);
            cell12.setCellValue("普通员工年平均工资");
            cell12.setCellStyle(dataStyle);
            sheet.addMergedRegion(new CellRangeAddress(1,2,12,12));

            Cell cell13=row1.createCell(13);
            cell13.setCellValue("备注");
            cell13.setCellStyle(dataStyle);
            sheet.addMergedRegion(new CellRangeAddress(1,2,13,13));

            Row row2=sheet.createRow(2);
            row2.setHeightInPoints(25);

            Cell cel=row2.createCell(0);
            cel.setCellValue("");
            cel.setCellStyle(dataStyle);

            Cell cel0=row2.createCell(1);
            cel0.setCellValue("");
            cel0.setCellStyle(dataStyle);


            Cell cel1=row2.createCell(2);
            cel1.setCellValue("合计");
            cel1.setCellStyle(dataStyle);

            Cell cel2=row2.createCell(3);
            cel2.setCellValue("高管人员工资总额");
            cel2.setCellStyle(dataStyle);

            Cell cel3=row2.createCell(4);
            cel3.setCellValue("中层管理人员工资总额");
            cel3.setCellStyle(dataStyle);

            Cell cel5=row2.createCell(5);
            cel5.setCellValue("员工工资总额");
            cel5.setCellStyle(dataStyle);

            Cell cel6=row2.createCell(6);
            cel6.setCellValue("合计");
            cel6.setCellStyle(dataStyle);

            Cell cel7=row2.createCell(7);
            cel7.setCellValue("高管人数");
            cel7.setCellStyle(dataStyle);

            Cell cel8=row2.createCell(8);
            cel8.setCellValue("中层管理人员人数");
            cel8.setCellStyle(dataStyle);

            Cell cel9=row2.createCell(9);
            cel9.setCellValue("普通员工人数");
            cel9.setCellStyle(dataStyle);

            Cell cel10=row2.createCell(10);
            cel10.setCellValue("");
            cel10.setCellStyle(dataStyle);

            Cell cel11=row2.createCell(11);
            cel11.setCellValue("");
            cel11.setCellStyle(dataStyle);

            Cell cel12=row2.createCell(12);
            cel12.setCellValue("");
            cel12.setCellStyle(dataStyle);

            Cell cel13=row2.createCell(13);
            cel13.setCellValue("");
            cel13.setCellStyle(dataStyle);
            int x=2;
            for(int i=0;i<result.size();i++){
                x++;
                HrmDept dt=result.get(i);
                Row row3=sheet.createRow(x);
                row3.setHeightInPoints(25);
                Cell celll=row3.createCell(0);
                celll.setCellValue(i+1);
                celll.setCellStyle(dataStyle);

                Cell celll1=row3.createCell(1);
                celll1.setCellValue(dt.getName());
                celll1.setCellStyle(dataStyle);

                Cell celll2=row3.createCell(2);
                celll2.setCellValue("");
                celll2.setCellStyle(dataStyle);

                Cell celll3=row3.createCell(3);
                celll3.setCellValue("");
                celll3.setCellStyle(dataStyle);

                Cell celll4=row3.createCell(4);
                celll4.setCellValue("");
                celll4.setCellStyle(dataStyle);

                Cell celll5=row3.createCell(5);
                celll5.setCellValue("");
                celll5.setCellStyle(dataStyle);

                Cell celll6=row3.createCell(6);
                celll6.setCellValue("");
                celll6.setCellStyle(dataStyle);

                Cell celll7=row3.createCell(7);
                celll7.setCellValue("");
                celll7.setCellStyle(dataStyle);

                Cell celll8=row3.createCell(8);
                celll8.setCellValue("");
                celll8.setCellStyle(dataStyle);

                Cell celll9=row3.createCell(9);
                celll9.setCellValue("");
                celll9.setCellStyle(dataStyle);

                Cell celll10=row3.createCell(10);
                celll10.setCellValue("");
                celll10.setCellStyle(dataStyle);

                Cell celll11=row3.createCell(11);
                celll11.setCellValue("");
                celll11.setCellStyle(dataStyle);

                Cell celll12=row3.createCell(12);
                celll12.setCellValue("");
                celll12.setCellStyle(dataStyle);

                Cell celll13=row3.createCell(13);
                celll13.setCellValue("");
                celll13.setCellStyle(dataStyle);
            }

            long timestamp = new Date().getTime();
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setCharacterEncoding("UTF-8");
            //test.xls是弹出下载对话框的文件名，不能为中文，中文请自行编码
            response.setHeader("Content-Disposition", "attachment;filename="+timestamp+"perCapitaSalaryExport.xls");
            ServletOutputStream out = response.getOutputStream();
            writer.flush(out);
        }catch (Exception e){
            log.error("导出统计报表-薪酬记录错误：", e);
        }
    }


    @PostMapping("/employeeStructureExport")
    @ApiOperation("从业人员人数及人员结构统计报表导出")
    public void employeeStructureExport( HttpServletResponse response,String deptId) {
        //EmployeeNumVO result = hrmSalaryReportService.queryEmployeeNum(deptId);
        try {
            List<EmployeeNumVO> result = hrmSalaryReportService.queryEmployeeNum();
            ExcelWriter writer = ExcelUtil.getWriter();
            for(int i=0;i<33;i++){writer.setColumnWidth(i,30);}
            Sheet sheet = writer.getSheet();
            CellRangeAddress cellAddresses=new CellRangeAddress(0,0,0,33);
            sheet.addMergedRegion(cellAddresses);

            /*标题以及内容样式 begin*/
            CellStyle headStyle = writer.createCellStyle();
            Font font =writer.createFont();
            font.setBold(true);
            font.setFontHeightInPoints((short) 16);
            headStyle.setFont(font);
            headStyle.setAlignment(HorizontalAlignment.CENTER);
            headStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
            headStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            headStyle.setBorderTop(BorderStyle.THIN);
            headStyle.setBorderLeft(BorderStyle.THIN);
            headStyle.setBorderRight(BorderStyle.THIN);
            headStyle.setBorderBottom(BorderStyle.THIN);

            CellStyle dataStyle = writer.createCellStyle();
            dataStyle.setBorderTop(BorderStyle.THIN);
            dataStyle.setBorderLeft(BorderStyle.THIN);
            dataStyle.setBorderRight(BorderStyle.THIN);
            dataStyle.setBorderBottom(BorderStyle.THIN);
            dataStyle.setAlignment(HorizontalAlignment.CENTER);
            dataStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            dataStyle.setWrapText(true);
            Font dataFont =writer.createFont();
            dataFont.setFontHeightInPoints((short) 14);
            dataStyle.setFont(dataFont);
            /*标题以及内容样式 end*/

            /*在岗职工人数、劳务派遣人数、劳务外包和其他从业人员人数 表格begin*/
            Row row0=sheet.createRow(0);
            row0.setHeightInPoints(30);
            Cell cell0=row0.createCell(0);
            cell0.setCellValue("从业人员人数及人员结构");
            cell0.setCellStyle(headStyle);

            Row row1=sheet.createRow(1);
            row1.setHeightInPoints(25);

            Cell cell=row1.createCell(0);
            cell.setCellValue("序号");
            cell.setCellStyle(dataStyle);
            sheet.addMergedRegion(new CellRangeAddress(1,2,0,0));

            Cell cell1=row1.createCell(1);
            cell1.setCellValue("单位名称");
            cell1.setCellStyle(dataStyle);
            sheet.addMergedRegion(new CellRangeAddress(1,2,1,1));

            Cell cell2=row1.createCell(2);
            cell2.setCellValue("合计");
            cell2.setCellStyle(dataStyle);
            sheet.addMergedRegion(new CellRangeAddress(1,2,2,2));

            Cell cell3=row1.createCell(3);
            cell3.setCellValue("从业人员期末人数");
            cell3.setCellStyle(dataStyle);
            sheet.addMergedRegion(new CellRangeAddress(1,1,3,7));

            Cell cell4=row1.createCell(4);
            cell4.setCellValue("");
            cell4.setCellStyle(dataStyle);

            Cell cell5=row1.createCell(5);
            cell5.setCellValue("");
            cell5.setCellStyle(dataStyle);

            Cell cell6=row1.createCell(6);
            cell6.setCellValue("");
            cell6.setCellStyle(dataStyle);


            Cell cell7=row1.createCell(7);
            cell7.setCellValue("");
            cell7.setCellStyle(dataStyle);

            Cell cell8=row1.createCell(8);
            cell8.setCellValue("合计");
            cell8.setCellStyle(dataStyle);
            sheet.addMergedRegion(new CellRangeAddress(1,2,8,8));

            Cell cell9=row1.createCell(9);
            cell9.setCellValue("年龄");
            cell9.setCellStyle(dataStyle);
            sheet.addMergedRegion(new CellRangeAddress(1,1,9,14));

            Cell cell10=row1.createCell(10);
            cell10.setCellValue("");
            cell10.setCellStyle(dataStyle);

            Cell cell11=row1.createCell(11);
            cell11.setCellValue("");
            cell11.setCellStyle(dataStyle);

            Cell cell12=row1.createCell(12);
            cell12.setCellValue("");
            cell12.setCellStyle(dataStyle);

            Cell cell13=row1.createCell(13);
            cell13.setCellValue("");
            cell13.setCellStyle(dataStyle);

            Cell cell14=row1.createCell(14);
            cell14.setCellValue("");
            cell14.setCellStyle(dataStyle);

            Cell cell15=row1.createCell(15);
            cell15.setCellValue("合计");
            cell15.setCellStyle(dataStyle);
            sheet.addMergedRegion(new CellRangeAddress(1,2,15,15));

            Cell cell16=row1.createCell(16);
            cell16.setCellValue("学历");
            cell16.setCellStyle(dataStyle);
            sheet.addMergedRegion(new CellRangeAddress(1,1,16,21));

            Cell cell17=row1.createCell(17);
            cell17.setCellValue("");
            cell17.setCellStyle(dataStyle);

            Cell cell18=row1.createCell(18);
            cell18.setCellValue("");
            cell18.setCellStyle(dataStyle);

            Cell cell19=row1.createCell(19);
            cell19.setCellValue("");
            cell19.setCellStyle(dataStyle);

            Cell cell20=row1.createCell(20);
            cell20.setCellValue("");
            cell20.setCellStyle(dataStyle);

            Cell cell21=row1.createCell(21);
            cell21.setCellValue("");
            cell21.setCellStyle(dataStyle);

            Cell cell22=row1.createCell(22);
            cell22.setCellValue("合计");
            cell22.setCellStyle(dataStyle);
            sheet.addMergedRegion(new CellRangeAddress(1,2,22,22));

            Cell cell23=row1.createCell(23);
            cell23.setCellValue("专业技术职务");
            cell23.setCellStyle(dataStyle);
            sheet.addMergedRegion(new CellRangeAddress(1,1,23,27));

            Cell cell24=row1.createCell(24);
            cell24.setCellValue("");
            cell24.setCellStyle(dataStyle);

            Cell cell25=row1.createCell(25);
            cell25.setCellValue("");
            cell25.setCellStyle(dataStyle);

            Cell cell26=row1.createCell(26);
            cell26.setCellValue("");
            cell26.setCellStyle(dataStyle);

            Cell cell33=row1.createCell(27);
            cell33.setCellValue("");
            cell33.setCellStyle(dataStyle);

            Cell cell27=row1.createCell(28);
            cell27.setCellValue("合计");
            cell27.setCellStyle(dataStyle);
            sheet.addMergedRegion(new CellRangeAddress(1,2,28,28));

            Cell cell28=row1.createCell(29);
            cell28.setCellValue("技术工人");
            cell28.setCellStyle(dataStyle);
            sheet.addMergedRegion(new CellRangeAddress(1,1,29,33));

            Cell cell29=row1.createCell(30);
            cell29.setCellValue("");
            cell29.setCellStyle(dataStyle);

            Cell cell30=row1.createCell(31);
            cell30.setCellValue("");
            cell30.setCellStyle(dataStyle);

            Cell cell31=row1.createCell(32);
            cell31.setCellValue("");
            cell31.setCellStyle(dataStyle);

            Cell cell32=row1.createCell(33);
            cell32.setCellValue("");
            cell32.setCellStyle(dataStyle);


            Row row2=sheet.createRow(2);
            row2.setHeightInPoints(25);

            Cell cel=row2.createCell(0);
            cel.setCellValue("");
            cel.setCellStyle(dataStyle);

            Cell cel0=row2.createCell(1);
            cel0.setCellValue("");
            cel0.setCellStyle(dataStyle);

            Cell cel1=row2.createCell(2);
            cel1.setCellValue("");
            cel1.setCellStyle(dataStyle);

            Cell cel2=row2.createCell(3);
            cel2.setCellValue("其中：女性");
            cel2.setCellStyle(dataStyle);

            Cell cel3=row2.createCell(4);
            cel3.setCellValue("在岗职工");
            cel3.setCellStyle(dataStyle);

            Cell cel5=row2.createCell(5);
            cel5.setCellValue("劳务派遣人员");
            cel5.setCellStyle(dataStyle);

            Cell cel6=row2.createCell(6);
            cel6.setCellValue("劳务外包");
            cel6.setCellStyle(dataStyle);

            Cell cel7=row2.createCell(7);
            cel7.setCellValue("其他从业人员");
            cel7.setCellStyle(dataStyle);

            Cell cel8=row2.createCell(8);
            cel8.setCellValue("");
            cel8.setCellStyle(dataStyle);

            Cell cel9=row2.createCell(9);
            cel9.setCellValue("35岁及以下");
            cel9.setCellStyle(dataStyle);

            Cell cel10=row2.createCell(10);
            cel10.setCellValue("36-40岁");
            cel10.setCellStyle(dataStyle);

            Cell cel11=row2.createCell(11);
            cel11.setCellValue("41-45岁");
            cel11.setCellStyle(dataStyle);

            Cell cel12=row2.createCell(12);
            cel12.setCellValue("46-50岁");
            cel12.setCellStyle(dataStyle);

            Cell cel13=row2.createCell(13);
            cel13.setCellValue("51-54岁");
            cel13.setCellStyle(dataStyle);

            Cell cel14=row2.createCell(14);
            cel14.setCellValue("55岁及以上");
            cel14.setCellStyle(dataStyle);

            Cell cel15=row2.createCell(15);
            cel15.setCellValue("");
            cel15.setCellStyle(dataStyle);

            Cell cel16=row2.createCell(16);
            cel16.setCellValue("博士研究生");
            cel16.setCellStyle(dataStyle);

            Cell cel17=row2.createCell(17);
            cel17.setCellValue("硕士研究生（含硕士或研究生)");
            cel17.setCellStyle(dataStyle);

            Cell cel18=row2.createCell(18);
            cel18.setCellValue("大学本科");
            cel18.setCellStyle(dataStyle);

            Cell cel19=row2.createCell(19);
            cel19.setCellValue("大学专科");
            cel19.setCellStyle(dataStyle);

            Cell cel20=row2.createCell(20);
            cel20.setCellValue("中专");
            cel20.setCellStyle(dataStyle);

            Cell cel21=row2.createCell(21);
            cel21.setCellValue("高中及以下");
            cel21.setCellStyle(dataStyle);

            Cell cel22=row2.createCell(22);
            cel22.setCellValue("");
            cel22.setCellStyle(dataStyle);

            Cell cel23=row2.createCell(23);
            cel23.setCellValue("正高级");
            cel23.setCellStyle(dataStyle);

            Cell cel24=row2.createCell(24);
            cel24.setCellValue("副高级");
            cel24.setCellStyle(dataStyle);

            Cell cel25=row2.createCell(25);
            cel25.setCellValue("中级");
            cel25.setCellStyle(dataStyle);

            Cell cel26=row2.createCell(26);
            cel26.setCellValue("助理级");
            cel26.setCellStyle(dataStyle);

            Cell cel33=row2.createCell(27);
            cel33.setCellValue("员级");
            cel33.setCellStyle(dataStyle);

            Cell cel27=row2.createCell(28);
            cel27.setCellValue("");
            cel27.setCellStyle(dataStyle);

            Cell cel28=row2.createCell(29);
            cel28.setCellValue("高级技师");
            cel28.setCellStyle(dataStyle);

            Cell cel29=row2.createCell(30);
            cel29.setCellValue("技师");
            cel29.setCellStyle(dataStyle);

            Cell cel30=row2.createCell(31);
            cel30.setCellValue("高级工");
            cel30.setCellStyle(dataStyle);

            Cell cel31=row2.createCell(32);
            cel31.setCellValue("中级工");
            cel31.setCellStyle(dataStyle);

            Cell cel32=row2.createCell(33);
            cel32.setCellValue("初级工");
            cel32.setCellStyle(dataStyle);

            int x=2;
            EmployeeNumVO emp=new  EmployeeNumVO();//用于合计
            int num=0;
            for(int i=0;i<result.size();i++){
                num+=result.get(i).getWomanNum();
                x++;
                EmployeeNumVO empvo=result.get(i);
                Row row3=sheet.createRow(x);
                row3.setHeightInPoints(25);
                Cell celll=row3.createCell(0);
                celll.setCellValue(i+1);
                celll.setCellStyle(dataStyle);

                Cell celll1=row3.createCell(1);
                celll1.setCellValue(empvo.getDeptName());
                celll1.setCellStyle(dataStyle);

                Cell celll2=row3.createCell(2);
                Integer value=empvo.getEmployeeNum()+empvo.getDispatchNum()+empvo.getOutNum()+empvo.getLaborerNum()+empvo.getReEmployerNum()+empvo.getOtherNum();
                celll2.setCellValue(value);
                celll2.setCellStyle(dataStyle);
                emp.setNum1(emp.getNum1()+value);

                Cell celll3=row3.createCell(3);
                celll3.setCellValue(empvo.getWomanNum());
                celll3.setCellStyle(dataStyle);
                emp.setWomanNum(emp.getWomanNum()+empvo.getWomanNum());

                Cell celll4=row3.createCell(4);
                celll4.setCellValue(empvo.getEmployeeNum());
                celll4.setCellStyle(dataStyle);
                emp.setEmployeeNum(emp.getEmployeeNum()+empvo.getEmployeeNum());

                Cell celll5=row3.createCell(5);
                celll5.setCellValue(empvo.getDispatchNum());
                celll5.setCellStyle(dataStyle);
                emp.setDispatchNum(emp.getDispatchNum()+empvo.getDispatchNum());

                Cell celll6=row3.createCell(6);
                celll6.setCellValue(empvo.getOutNum());
                celll6.setCellStyle(dataStyle);
                emp.setOutNum(emp.getOutNum()+empvo.getOutNum());

                Cell celll7=row3.createCell(7);
                Integer value1=empvo.getLaborerNum()+empvo.getReEmployerNum()+empvo.getOtherNum();
                celll7.setCellValue(value1);
                celll7.setCellStyle(dataStyle);
                emp.setOtherNum(emp.getOtherNum()+value1);

                Cell celll8=row3.createCell(8);
                Integer value3=empvo.getUnder35Num()+empvo.getBetween36Num()+empvo.getBetween41Num()+empvo.getBetween46Num()+empvo.getBetween51Num()+empvo.getAbove55Num();
                celll8.setCellValue(value3);
                celll8.setCellStyle(dataStyle);
                emp.setNum2(emp.getNum2()+value3);

                Cell celll9=row3.createCell(9);
                celll9.setCellValue(empvo.getUnder35Num());
                celll9.setCellStyle(dataStyle);
                emp.setUnder35Num(emp.getUnder35Num()+empvo.getUnder35Num());

                Cell celll10=row3.createCell(10);
                celll10.setCellValue(empvo.getBetween36Num());
                celll10.setCellStyle(dataStyle);
                emp.setBetween36Num(emp.getBetween36Num()+empvo.getBetween36Num());

                Cell celll11=row3.createCell(11);
                celll11.setCellValue(empvo.getBetween41Num());
                celll11.setCellStyle(dataStyle);
                emp.setBetween41Num(emp.getBetween41Num()+empvo.getBetween41Num());

                Cell celll12=row3.createCell(12);
                celll12.setCellValue(empvo.getBetween46Num());
                celll12.setCellStyle(dataStyle);
                emp.setBetween46Num(emp.getBetween46Num()+empvo.getBetween46Num());

                Cell celll13=row3.createCell(13);
                celll13.setCellValue(empvo.getBetween51Num());
                celll13.setCellStyle(dataStyle);
                emp.setBetween51Num(emp.getBetween51Num()+empvo.getBetween51Num());

                Cell celll14=row3.createCell(14);
                celll14.setCellValue(empvo.getAbove55Num());
                celll14.setCellStyle(dataStyle);
                emp.setAbove55Num(emp.getAbove55Num()+empvo.getAbove55Num());

                Cell celll15=row3.createCell(15);
                Integer value4=empvo.getDoctorNum()+empvo.getMasterNum()+empvo.getUndergraduateNum()+empvo.getJuniorCollegeNum()+empvo.getSecondaryNum()+empvo.getHighSchoolNum();
                celll15.setCellValue(value4);
                celll15.setCellStyle(dataStyle);
                emp.setNum3(emp.getNum3()+value4);

                Cell celll16=row3.createCell(16);
                celll16.setCellValue(empvo.getDoctorNum());
                celll16.setCellStyle(dataStyle);
                emp.setDoctorNum(emp.getDoctorNum()+empvo.getDoctorNum());

                Cell celll17=row3.createCell(17);
                celll17.setCellValue(empvo.getMasterNum());
                celll17.setCellStyle(dataStyle);
                emp.setMasterNum(emp.getMasterNum()+empvo.getMasterNum());

                Cell celll18=row3.createCell(18);
                celll18.setCellValue(empvo.getUndergraduateNum());
                celll18.setCellStyle(dataStyle);
                emp.setUndergraduateNum(emp.getUndergraduateNum()+empvo.getUndergraduateNum());

                Cell celll19=row3.createCell(19);
                celll19.setCellValue(empvo.getJuniorCollegeNum());
                celll19.setCellStyle(dataStyle);
                emp.setJuniorCollegeNum(emp.getJuniorCollegeNum()+empvo.getJuniorCollegeNum());

                Cell celll20=row3.createCell(20);
                celll20.setCellValue(empvo.getSecondaryNum());
                celll20.setCellStyle(dataStyle);
                emp.setSecondaryNum(emp.getSecondaryNum()+empvo.getSecondaryNum());

                Cell celll21=row3.createCell(21);
                celll21.setCellValue(empvo.getHighSchoolNum());
                celll21.setCellStyle(dataStyle);
                emp.setHighSchoolNum(emp.getHighSchoolNum()+empvo.getHighSchoolNum());

                Cell celll22=row3.createCell(22);
                Integer value5=empvo.getHighNum()+empvo.getSubHighNum()+empvo.getMiddleNum()+empvo.getAssistantNum()+empvo.getNormalNum();
                celll22.setCellValue(value5);
                celll22.setCellStyle(dataStyle);
                emp.setNum4(emp.getNum4()+value5);

                Cell celll23=row3.createCell(23);
                celll23.setCellValue(empvo.getHighNum());
                celll23.setCellStyle(dataStyle);
                emp.setHighNum(emp.getHighNum()+empvo.getHighNum());

                Cell celll24=row3.createCell(24);
                celll24.setCellValue(empvo.getSubHighNum());
                celll24.setCellStyle(dataStyle);
                emp.setSubHighNum(emp.getSubHighNum()+empvo.getSubHighNum());

                Cell celll25=row3.createCell(25);
                celll25.setCellValue(empvo.getMiddleNum());
                celll25.setCellStyle(dataStyle);
                emp.setMiddleNum(emp.getMiddleNum()+empvo.getMiddleNum());

                Cell celll26=row3.createCell(26);
                celll26.setCellValue(empvo.getAssistantNum());
                celll26.setCellStyle(dataStyle);
                emp.setAssistantNum(emp.getAssistantNum()+empvo.getAssistantNum());

                Cell celll27=row3.createCell(27);
                celll27.setCellValue(empvo.getNormalNum());
                celll27.setCellStyle(dataStyle);
                emp.setNormalNum(emp.getNormalNum()+empvo.getNormalNum());

                Cell celll28=row3.createCell(28);
                Integer value6=empvo.getSeniorTechnicianNum()+empvo.getTechnicianNum()+empvo.getSeniorEngineerNum()+empvo.getIntermediateNum()+empvo.getJuniorNum();
                celll28.setCellValue(value6);
                celll28.setCellStyle(dataStyle);
                emp.setNum5(emp.getNum5()+value6);

                Cell celll29=row3.createCell(29);
                celll29.setCellValue(empvo.getSeniorTechnicianNum());
                celll29.setCellStyle(dataStyle);
                emp.setSeniorTechnicianNum(emp.getSeniorTechnicianNum()+empvo.getSeniorTechnicianNum());

                Cell celll30=row3.createCell(30);
                celll30.setCellValue(empvo.getTechnicianNum());
                celll30.setCellStyle(dataStyle);
                emp.setTechnicianNum(emp.getTechnicianNum()+empvo.getTechnicianNum());

                Cell celll31=row3.createCell(31);
                celll31.setCellValue(empvo.getSeniorEngineerNum());
                celll31.setCellStyle(dataStyle);
                emp.setSeniorEngineerNum(emp.getSeniorEngineerNum()+empvo.getSeniorEngineerNum());

                Cell celll32=row3.createCell(32);
                celll32.setCellValue(empvo.getIntermediateNum());
                celll32.setCellStyle(dataStyle);
                emp.setIntermediateNum(emp.getIntermediateNum()+empvo.getIntermediateNum());

                Cell celll33=row3.createCell(33);
                celll33.setCellValue(empvo.getJuniorNum());
                celll33.setCellStyle(dataStyle);
                emp.setJuniorNum(emp.getJuniorNum()+empvo.getJuniorNum());
            }
            System.out.println(num);
            Row row3=sheet.createRow(x+1);
            row3.setHeightInPoints(25);
            Cell celll=row3.createCell(0);
            celll.setCellValue(result.size()+1);
            celll.setCellStyle(dataStyle);

            Cell celll1=row3.createCell(1);
            celll1.setCellValue("合计");
            celll1.setCellStyle(dataStyle);

            Cell celll2=row3.createCell(2);
            celll2.setCellValue(emp.getNum1());
            celll2.setCellStyle(dataStyle);

            Cell celll3=row3.createCell(3);
            celll3.setCellValue(emp.getWomanNum());
            celll3.setCellStyle(dataStyle);

            Cell celll4=row3.createCell(4);
            celll4.setCellValue(emp.getEmployeeNum());
            celll4.setCellStyle(dataStyle);

            Cell celll5=row3.createCell(5);
            celll5.setCellValue(emp.getDispatchNum());
            celll5.setCellStyle(dataStyle);

            Cell celll6=row3.createCell(6);
            celll6.setCellValue(emp.getOutNum());
            celll6.setCellStyle(dataStyle);

            Cell celll7=row3.createCell(7);
            celll7.setCellValue(emp.getOtherNum());
            celll7.setCellStyle(dataStyle);

            Cell celll8=row3.createCell(8);
            celll8.setCellValue(emp.getNum2());
            celll8.setCellStyle(dataStyle);

            Cell celll9=row3.createCell(9);
            celll9.setCellValue(emp.getUnder35Num());
            celll9.setCellStyle(dataStyle);

            Cell celll10=row3.createCell(10);
            celll10.setCellValue(emp.getBetween36Num());
            celll10.setCellStyle(dataStyle);

            Cell celll11=row3.createCell(11);
            celll11.setCellValue(emp.getBetween41Num());
            celll11.setCellStyle(dataStyle);

            Cell celll12=row3.createCell(12);
            celll12.setCellValue(emp.getBetween46Num());
            celll12.setCellStyle(dataStyle);

            Cell celll13=row3.createCell(13);
            celll13.setCellValue(emp.getBetween51Num());
            celll13.setCellStyle(dataStyle);

            Cell celll14=row3.createCell(14);
            celll14.setCellValue(emp.getAbove55Num());
            celll14.setCellStyle(dataStyle);

            Cell celll15=row3.createCell(15);
            celll15.setCellValue(emp.getNum3());
            celll15.setCellStyle(dataStyle);

            Cell celll16=row3.createCell(16);
            celll16.setCellValue(emp.getDoctorNum());
            celll16.setCellStyle(dataStyle);

            Cell celll17=row3.createCell(17);
            celll17.setCellValue(emp.getMasterNum());
            celll17.setCellStyle(dataStyle);

            Cell celll18=row3.createCell(18);
            celll18.setCellValue(emp.getUndergraduateNum());
            celll18.setCellStyle(dataStyle);

            Cell celll19=row3.createCell(19);
            celll19.setCellValue(emp.getJuniorCollegeNum());
            celll19.setCellStyle(dataStyle);

            Cell celll20=row3.createCell(20);
            celll20.setCellValue(emp.getSecondaryNum());
            celll20.setCellStyle(dataStyle);

            Cell celll21=row3.createCell(21);
            celll21.setCellValue(emp.getHighSchoolNum());
            celll21.setCellStyle(dataStyle);

            Cell celll22=row3.createCell(22);
            celll22.setCellValue(emp.getNum4());
            celll22.setCellStyle(dataStyle);

            Cell celll23=row3.createCell(23);
            celll23.setCellValue(emp.getHighNum());
            celll23.setCellStyle(dataStyle);

            Cell celll24=row3.createCell(24);
            celll24.setCellValue(emp.getSubHighNum());
            celll24.setCellStyle(dataStyle);

            Cell celll25=row3.createCell(25);
            celll25.setCellValue(emp.getMiddleNum());
            celll25.setCellStyle(dataStyle);

            Cell celll26=row3.createCell(26);
            celll26.setCellValue(emp.getAssistantNum());
            celll26.setCellStyle(dataStyle);

            Cell celll27=row3.createCell(27);
            celll27.setCellValue(emp.getNormalNum());
            celll27.setCellStyle(dataStyle);

            Cell celll28=row3.createCell(28);
            celll28.setCellValue(emp.getNum5());
            celll28.setCellStyle(dataStyle);

            Cell celll29=row3.createCell(29);
            celll29.setCellValue(emp.getSeniorTechnicianNum());
            celll29.setCellStyle(dataStyle);

            Cell celll30=row3.createCell(30);
            celll30.setCellValue(emp.getTechnicianNum());
            celll30.setCellStyle(dataStyle);

            Cell celll31=row3.createCell(31);
            celll31.setCellValue(emp.getSeniorEngineerNum());
            celll31.setCellStyle(dataStyle);

            Cell celll32=row3.createCell(32);
            celll32.setCellValue(emp.getIntermediateNum());
            celll32.setCellStyle(dataStyle);

            Cell celll33=row3.createCell(33);
            celll33.setCellValue(emp.getJuniorNum());
            celll33.setCellStyle(dataStyle);

            long timestamp = new Date().getTime();
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setCharacterEncoding("UTF-8");
            //test.xls是弹出下载对话框的文件名，不能为中文，中文请自行编码
            response.setHeader("Content-Disposition", "attachment;filename="+timestamp+"employeeStructureExport.xls");
            ServletOutputStream out = response.getOutputStream();
            writer.flush(out);
        }catch (Exception e){
            log.error("导出统计报表-薪酬记录错误：", e);
        }
    }



    @PostMapping("/humanResourcesExport")
    @ApiOperation("企业人力资源指标年报表导出")
    public void humanResourcesExport( HttpServletResponse response,String deptId) {
        //EmployeeNumVO result = hrmSalaryReportService.queryEmployeeNum(deptId);
        try {
            ExcelWriter writer = ExcelUtil.getWriter();
            for(int i=0;i<3;i++){writer.setColumnWidth(i,30);}
            Sheet sheet = writer.getSheet();
            CellRangeAddress cellAddresses=new CellRangeAddress(0,0,0,3);
            sheet.addMergedRegion(cellAddresses);

            /*标题以及内容样式 begin*/
            CellStyle headStyle = writer.createCellStyle();
            Font font =writer.createFont();
            font.setBold(true);
            font.setFontHeightInPoints((short) 16);
            headStyle.setFont(font);
            headStyle.setAlignment(HorizontalAlignment.CENTER);
            headStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
            headStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            headStyle.setBorderTop(BorderStyle.THIN);
            headStyle.setBorderLeft(BorderStyle.THIN);
            headStyle.setBorderRight(BorderStyle.THIN);
            headStyle.setBorderBottom(BorderStyle.THIN);

            CellStyle dataStyle = writer.createCellStyle();
            dataStyle.setBorderTop(BorderStyle.THIN);
            dataStyle.setBorderLeft(BorderStyle.THIN);
            dataStyle.setBorderRight(BorderStyle.THIN);
            dataStyle.setBorderBottom(BorderStyle.THIN);
            dataStyle.setAlignment(HorizontalAlignment.CENTER);
            dataStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            dataStyle.setWrapText(true);
            Font dataFont =writer.createFont();
            dataFont.setFontHeightInPoints((short) 14);
            dataStyle.setFont(dataFont);
            /*标题以及内容样式 end*/

            SimpleDateFormat sdf=new SimpleDateFormat("yyyy");

            /*在岗职工人数、劳务派遣人数、劳务外包和其他从业人员人数 表格begin*/
            Row row0=sheet.createRow(0);
            row0.setHeightInPoints(30);
            Cell cell0=row0.createCell(0);
            cell0.setCellValue("企业人力资源指标年报");
            cell0.setCellStyle(headStyle);

            Row row1=sheet.createRow(1);
            row1.setHeightInPoints(25);

            Cell cell=row1.createCell(0);
            cell.setCellValue("指标项目");
            cell.setCellStyle(dataStyle);
            sheet.addMergedRegion(new CellRangeAddress(1,2,0,0));


            Cell cell1=row1.createCell(1);
            cell1.setCellValue(sdf.format(new Date())+"年");
            cell1.setCellStyle(dataStyle);
            sheet.addMergedRegion(new CellRangeAddress(1,1,1,2));

            Cell cell2=row1.createCell(2);
            cell2.setCellValue("");
            cell2.setCellStyle(dataStyle);

            Cell cell3=row1.createCell(3);
            cell3.setCellValue("备注");
            cell3.setCellStyle(dataStyle);
            sheet.addMergedRegion(new CellRangeAddress(1,2,3,3));


            Row row2=sheet.createRow(2);
            row2.setHeightInPoints(25);

            Cell cel=row2.createCell(0);
            cel.setCellValue("");
            cel.setCellStyle(dataStyle);

            Cell cel0=row2.createCell(1);
            cel0.setCellValue("期末数");
            cel0.setCellStyle(dataStyle);

            Cell cel1=row2.createCell(2);
            cel1.setCellValue("平均数");
            cel1.setCellStyle(dataStyle);

            Cell cel2=row2.createCell(3);
            cel2.setCellValue("");
            cel2.setCellStyle(dataStyle);
            String jsonString="[" +
                    "{'name':'一、人员管理（单位：人）','value':'-','value1':'-','value2':''}" +
                    ",{'name':'1、企业从业人员人数','value':'13580','value1':'13542','value2':''}" +
                    ",{'name':'  其中：在岗职工','value':'11295','value1':'11225','value2':''}" +
                    ",{'name':'           其中：新入职职工人数','value':'582','value1':'224.663333333333','value2':''}" +
                    ",{'name':'       劳务派遣人员','value':'1022','value1':'1103','value2':''}" +
                    ",{'name':'       其他从业人员（含外包）','value':'1263','value1':'1214','value2':''}" +
                    ",{'name':'2、企业离、退休职工人数','value':'2350','value1':'2263','value2':''}" +
                    ",{'name':'3、企业内退、不在岗职工人数','value':'324','value1':'334','value2':''}" +
                    ",{'name':'4、当年离职人数','value':'841','value1':'253.833333333333','value2':''}" +
                    ",{'name':'5、企业经营管理人员人数','value':'2960','value1':'2972.49666666667','value2':'集团所属一级单位（含比照）'}" +
                    ",{'name':'  其中：高管人员','value':'238','value1':'242.5','value2':''}" +
                    ",{'name':'       中层管理人员','value':'1245','value1':'1223','value2':''}" +
                    ",{'name':'       一般管理人员','value':'1477','value1':'1507.49666666667','value2':''}" +
                    ",{'name':'6、取得专业技术职务任职资格人数','value':'2457','value1':'2376.49666666667','value2':''}" +
                    ",{'name':'  其中：高级职称（含正高）','value':'282','value1':'276','value2':''}" +
                    ",{'name':'       中级职称','value':'935','value1':'914.746666666667','value2':''}" +
                    ",{'name':'       初级职称（含助理、员级）','value':'1240','value1':'1185.75','value2':''}" +
                    ",{'name':'7、取得技能职业资格人数','value':'952','value1':'950','value2':''}" +
                    ",{'name':'  其中：高级技师','value':'80','value1':'78','value2':''}" +
                    ",{'name':'       技师','value':'157','value1':'158','value2':''}" +
                    ",{'name':'       高级工','value':'301','value1':'299','value2':''}" +
                    ",{'name':'       中级工','value':'414','value1':'415','value2':''}" +
                    ",{'name':'8、学历情况','value':'7924','value1':'7788.66','value2':'以最高学历统计'}" +
                    ",{'name':'  其中：博士研究生人数','value':'5','value1':'5','value2':''}" +
                    ",{'name':'       硕士研究生人数（含硕士）','value':'358','value1':'358','value2':''}" +
                    ",{'name':'       大学本科人数','value':'3918','value1':'3836.5','value2':''}" +
                    ",{'name':'       大学专科人数','value':'3643','value1':'3594.66','value2':''}" +
                    ",{'name':'二、企业人工成本总额（单位：万元）','value':'219529.2204358','value1':'-','value2':''}" +
                    ",{'name':'1、在岗职工工资总额','value':'149756.074774','value1':'-','value2':''}" +
                    ",{'name':'  其中：基本工资','value':'65514.115339','value1':'-','value2':''}" +
                    ",{'name':'       绩效工资','value':'61974.475602','value1':'-','value2':''}" +
                    ",{'name':'       其他工资','value':'22267.483833','value1':'-','value2':''}" +
                    ",{'name':'2、劳务派遣人员费用','value':'7567.398333','value1':'-','value2':''}" +
                    ",{'name':'3、劳务外包人员费用','value':'7066.586188','value1':'-','value2':''}" +
                    ",{'name':'4、支付离、退休职工费用','value':'434.872832','value1':'-','value2':''}" +
                    ",{'name':'5、支付内退、不在岗职工费用','value':'925.920405','value1':'-','value2':''}" +
                    ",{'name':'6、福利费','value':'13164.123142','value1':'-','value2':''}" +
                    ",{'name':'7、五险费用','value':'20361.7021188','value1':'-','value2':''}" +
                    ",{'name':'8、公积金费用','value':'10683.290515','value1':'-','value2':''}" +
                    ",{'name':'9、年金费用','value':'5342.557325','value1':'-','value2':''}" +
                    ",{'name':'10、补充医疗保险费用','value':'970.683961','value1':'-','value2':''}" +
                    ",{'name':'11、培训费用','value':'270.664546','value1':'-','value2':''}" +
                    ",{'name':'12、招聘费用','value':'25.53','value1':'-','value2':''}" +
                    ",{'name':'13、工会会费','value':'1845.18263','value1':'-','value2':''}" +
                    ",{'name':'14、劳动保护费用','value':'680.516428','value1':'-','value2':''}" +
                    ",{'name':'15、其他','value':'434.117238','value1':'-','value2':''}" +
                    ",{'name':'三、人力资源效益指标','value':'','value1':'-','value2':''}" +
                    ",{'name':'1、在岗职工平均工资（元）','value':'133412.984208463','value1':'-','value2':''}" +
                    ",{'name':'2、在岗职工平均工资（不含外方）（元）','value':'','value1':'-','value2':'剔除子公司外方人员'}" +
                    ",{'name':'3、人工成本利润率（%）','value':'','value1':'-','value2':'利润总额/企业人工成本总额*100%'}" +
                    ",{'name':'4、人均利润（万元）','value':'','value1':'-','value2':'利润总额/从业人员平均人数'}" +
                    ",{'name':'5、人均人工成本（万元）','value':'13.6  ','value1':'-','value2':'企业人工成本总额/员工平均人数（员工指：从业人员和内退不在岗、离退休人员）'}" +
                    ",{'name':'   其中：从业人员人均人工成本（万元）','value':'16.21','value1':'-','value2':'企业人工成本总额/从业人员平均人数'}" +
                    ",{'name':'6、人均营业收入（万元）','value':'','value1':'-','value2':'营业收入/从业人员平均人数'}" +
                    ",{'name':'7、人事费用率（%）','value':'','value1':'-','value2':'企业人工成本总额/营业收入*100%'}]";

            JSONArray jsonArray = JSONArray.parseArray(jsonString) ;
            int x =3;
            for (int i = 0; i < jsonArray.size(); i++) {
                JSONObject jsonObject = jsonArray.getJSONObject(i);
                System.out.println(jsonObject.getString("name"));
                System.out.println(jsonObject.getString("value"));
                System.out.println(jsonObject.getString("value1"));
                System.out.println(jsonObject.getString("value2"));

                Row rows=sheet.createRow(x);
                rows.setHeightInPoints(25);

                Cell celx=rows.createCell(0);
                celx.setCellValue(jsonObject.getString("name"));
                celx.setCellStyle(dataStyle);

                Cell celx1=rows.createCell(1);
                celx1.setCellValue(jsonObject.getString("value"));
                celx1.setCellStyle(dataStyle);

                Cell celx2=rows.createCell(02);
                celx2.setCellValue(jsonObject.getString("value1"));
                celx2.setCellStyle(dataStyle);

                Cell celx3=rows.createCell(3);
                celx3.setCellValue(jsonObject.getString("value2"));
                celx3.setCellStyle(dataStyle);
                x++;
            }

            long timestamp = new Date().getTime();
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setCharacterEncoding("UTF-8");
            //test.xls是弹出下载对话框的文件名，不能为中文，中文请自行编码
            response.setHeader("Content-Disposition", "attachment;filename="+timestamp+"humanResourcesExport.xls");
            ServletOutputStream out = response.getOutputStream();
            writer.flush(out);
        }catch (Exception e){
            log.error("导出统计报表-薪酬记录错误：", e);
        }
    }
}
